﻿Imports System.IO
Imports System.Text

Public Class DBConnect
#Region "Database Related Functions"

    Public Shared ConnectionString As String = ""

    Public Shared Sub ExecuteSQL(ByVal CN As String, ByVal SQL As String)
        ExecuteSQL(CN, Sql, 35)
    End Sub

    Public Shared Sub ExecuteSQL(ByVal CN As String, ByVal SQL As String, ByVal timeout As Integer)

        Dim conn As New System.Data.SqlClient.SqlConnection(CN)
        conn.Open()

        Dim tran As System.Data.SqlClient.SqlTransaction
        tran = conn.BeginTransaction(IsolationLevel.Serializable)

        Try

            ExecuteSQL(tran, SQL, timeout)

            tran.Commit()

        Catch ex As Exception

            tran.Rollback()
            Throw ex

        Finally

            DestroyObject(DirectCast(tran, System.Object))
            DestroyObject(DirectCast(cn, System.Object))

        End Try

    End Sub

    Public Shared Sub ExecuteSQL(ByVal tran As System.Data.SqlClient.SqlTransaction, ByVal sSQL As String)

        ExecuteSQL(tran, sSQL, 500)

    End Sub

    Public Shared Sub ExecuteSQL(ByRef tran As System.Data.SqlClient.SqlTransaction, ByVal SQL As String, ByVal timeout As Integer)

        If tran Is Nothing OrElse tran.Connection Is Nothing OrElse tran.Connection.State = ConnectionState.Closed Then
            Throw New Exception("Transaction object not properly set.  Ensure that the transaction object is not nothing, that the transaction object is associated with a connection, and that the connection is open.")
        End If

        Dim cn As System.Data.SqlClient.SqlConnection = tran.Connection
        Dim cmd As New System.Data.SqlClient.SqlCommand(SQL, cn, tran)

        Try

            cmd.CommandTimeout = timeout
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            Throw ex
        Finally
            DestroyObject(DirectCast(cmd, System.Object))
        End Try

    End Sub

    Public Shared Function OpenDataTable(ByVal cmd As System.Data.SqlClient.SqlCommand) As System.Data.DataTable

        Dim logTimeLimit As Integer
        logTimeLimit = FromString(System.Environment.GetEnvironmentVariable("QueryTimeLimit"), 15)

        Dim startTime As Date
        startTime = Now()

        Dim da As New System.Data.SqlClient.SqlDataAdapter(cmd)
        Dim dt As New System.Data.DataTable

        Try
            da.Fill(dt)
        Catch ex As Exception
            Throw ex
        Finally
            DestroyObject(DirectCast(da, System.Object))
        End Try

        Return dt
    End Function

    Public Shared Function OpenDataTable(ByVal cn As System.Data.SqlClient.SqlConnection, ByVal sql As String, Optional ByVal timeout As Integer = 35) As System.Data.DataTable

        Dim cmd As New System.Data.SqlClient.SqlCommand(sql, cn)
        cmd.CommandTimeout = timeout

        Dim dt As System.Data.DataTable

        Try
            dt = OpenDataTable(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            DestroyObject(DirectCast(cmd, System.Object))
        End Try

        Return dt

    End Function

    Public Shared Function OpenDataTable(ByVal connectionString As String, ByVal sql As String, Optional ByVal timeout As Integer = 35, Optional ByRef tran As System.Data.SqlClient.SqlTransaction = Nothing) As System.Data.DataTable

        connectionString = RemoveKeyword(RemoveKeyword(connectionString, "provider"), "driver")

        Dim cn As System.Data.SqlClient.SqlConnection
        Dim cmd As System.Data.SqlClient.SqlCommand

        If tran Is Nothing Then
            cn = New System.Data.SqlClient.SqlConnection(connectionString)
            cn.Open()
            cmd = New System.Data.SqlClient.SqlCommand(sql, cn)
        Else
            cn = tran.Connection
            cmd = New System.Data.SqlClient.SqlCommand(sql, cn, tran)
        End If

        cmd.CommandTimeout = timeout

        Dim dt As System.Data.DataTable

        Try

            dt = OpenDataTable(cmd)

        Catch ex As Exception

            Throw New Exception(sql, ex)

        Finally
            If tran Is Nothing Then
                cn.Close()
                DestroyObject(DirectCast(cn, System.Object))
            End If
            DestroyObject(DirectCast(cmd, System.Object))
        End Try

        Return dt

    End Function

    Public Shared Function ExecuteSQLScalar(ByVal cmd As System.Data.SqlClient.SqlCommand) As Object

        Dim logTimeLimit As Integer
        logTimeLimit = FromString(System.Environment.GetEnvironmentVariable("QueryTimeLimit"), 15)

        Dim startTime As Date
        startTime = Now()

        Dim ret As Object

        Try

            ret = cmd.ExecuteScalar()

        Catch ex As Exception

            Throw ex

        End Try
        Return ret

    End Function

    Public Shared Function ExecuteSQLScalar(ByVal cn As System.Data.SqlClient.SqlConnection, ByVal sql As String, ByVal params As SQLParameterArray, Optional ByVal timeout As Integer = 35) As Object

        Dim cmd As New System.Data.SqlClient.SqlCommand(sql, cn)
        cmd.CommandTimeout = timeout

        Dim ret As Object

        Try

            For Each p As SqlClient.SqlParameter In params.Parameters
                cmd.Parameters.Add(p)
            Next

            ret = OpenDataTable(cmd)

        Catch ex As Exception

            Throw ex

        Finally

            DestroyObject(DirectCast(cmd, System.Object))

        End Try

        Return ret

    End Function


    Public Shared Function ExecuteSQLScalar(ByVal cn As System.Data.SqlClient.SqlConnection, ByVal sql As String, Optional ByVal timeout As Integer = 35) As Object

        Dim cmd As New System.Data.SqlClient.SqlCommand(sql, cn)
        cmd.CommandTimeout = timeout

        Dim ret As Object

        Try

            ret = OpenDataTable(cmd)

        Catch ex As Exception

            Throw ex

        Finally

            DestroyObject(DirectCast(cmd, System.Object))

        End Try

        Return ret

    End Function


    Public Shared Function ExecuteSQLScalar(ByVal connectionString As String, ByVal sql As String, Optional ByVal timeout As Integer = 35, Optional ByRef tran As System.Data.SqlClient.SqlTransaction = Nothing) As Object

        connectionString = RemoveKeyword(RemoveKeyword(connectionString, "provider"), "driver")

        Dim cn As System.Data.SqlClient.SqlConnection
        Dim cmd As System.Data.SqlClient.SqlCommand

        If tran Is Nothing Then
            cn = New System.Data.SqlClient.SqlConnection(connectionString)
            cn.Open()
            cmd = New System.Data.SqlClient.SqlCommand(sql, cn)
        Else
            cn = tran.Connection
            cmd = New System.Data.SqlClient.SqlCommand(sql, cn, tran)
        End If

        cmd.CommandTimeout = timeout

        Dim ret As Object

        Try

            ret = ExecuteSQLScalar(cmd)

        Catch ex As Exception

            Throw ex

        Finally
            If tran Is Nothing Then
                cn.Close()
                DestroyObject(DirectCast(cn, System.Object))
            End If

            DestroyObject(DirectCast(cmd, System.Object))

        End Try

        Return ret

    End Function
    Public Shared Function ExecuteSQLScalar(ByVal connectionString As String, ByVal sql As String, ByVal params As SQLParameterArray, Optional ByVal timeout As Integer = 35, Optional ByRef tran As System.Data.SqlClient.SqlTransaction = Nothing) As Object

        connectionString = RemoveKeyword(RemoveKeyword(connectionString, "provider"), "driver")

        Dim cn As System.Data.SqlClient.SqlConnection
        Dim cmd As System.Data.SqlClient.SqlCommand

        If tran Is Nothing Then
            cn = New System.Data.SqlClient.SqlConnection(connectionString)
            cn.Open()
            cmd = New System.Data.SqlClient.SqlCommand(sql, cn)
        Else
            cn = tran.Connection
            cmd = New System.Data.SqlClient.SqlCommand(sql, cn, tran)
        End If

        cmd.CommandTimeout = timeout

        For Each p As SqlClient.SqlParameter In params.Parameters
            cmd.Parameters.Add(p)
        Next

        Dim ret As Object

        Try

            ret = ExecuteSQLScalar(cmd)

        Catch ex As Exception

            Throw ex

        Finally
            If tran Is Nothing Then
                cn.Close()
            End If
        End Try
        Return ret

    End Function

    Private Shared Function RemoveKeyword(ByVal connectionString As String, ByVal keyword As String) As String

        Dim startpos As Integer = InStr(connectionString, keyword, CompareMethod.Text)
        If startpos > 0 Then

            Return Replace(connectionString, Mid(connectionString, startpos, InStr(startpos, connectionString, ";", CompareMethod.Text) - startpos + 1), "")

        Else

            Return connectionString

        End If

    End Function

    Public Shared Sub DestroyObject(ByRef oObject As Object)

        If Not oObject Is Nothing Then

            Try

                If TypeOf oObject Is System.Data.SqlClient.SqlConnection AndAlso DirectCast(oObject, System.Data.SqlClient.SqlConnection).State = ConnectionState.Open Then
                    DirectCast(oObject, System.Data.SqlClient.SqlConnection).Close()
                End If

                If TypeOf oObject Is System.IO.StreamWriter Then
                    DirectCast(oObject, System.IO.StreamWriter).Close()
                End If

                If TypeOf oObject Is IDisposable Then
                    DirectCast(oObject, System.IDisposable).Dispose()
                End If

            Catch ex As Exception

                ' do nothing

            Finally

                oObject = Nothing

            End Try

        End If

    End Sub

    Public Shared Function FromString(ByVal value As String, ByVal defaultValue As System.Guid) As System.Guid

        If value = Nothing Then Return defaultValue
        Try
            Return New System.Guid(value)
        Catch ex As Exception
            Return defaultValue
        End Try

    End Function

    Public Shared Function FromString(ByVal value As String, ByVal defaultValue As Boolean) As Boolean

        If value = Nothing Then Return defaultValue
        Try
            Return CBool(value)
        Catch ex As Exception
            Return defaultValue
        End Try

    End Function

    Public Shared Function FromString(ByVal value As String, ByVal defaultValue As Integer) As Integer

        If value = Nothing Then Return defaultValue
        Try
            Return CInt(value)
        Catch ex As Exception
            Return defaultValue
        End Try

    End Function

    Public Shared Function FromString(ByVal value As String, ByVal defaultValue As Double) As Double

        If value = Nothing Then Return defaultValue
        Try
            Return CDbl(value)
        Catch ex As Exception
            Return defaultValue
        End Try

    End Function

    Public Shared Function FromString(ByVal value As String, ByVal defaultValue As Date) As Date

        If value = Nothing Then Return defaultValue
        Try
            Return CDate(value)
        Catch ex As Exception
            Return defaultValue
        End Try

    End Function
#End Region

    Public Class SQLParameterArray

        Private _parameters() As SqlClient.SqlParameter

        Public ReadOnly Property Parameters() As SqlClient.SqlParameter()
            Get
                Return _parameters
            End Get
        End Property

        Public ReadOnly Property Length() As Integer
            Get
                Return _parameters.Length
            End Get
        End Property

        Public Sub New()
            _parameters = New SqlClient.SqlParameter() {}
        End Sub

        Public Sub Add(ByVal name As String, ByVal type As SqlDbType, ByVal value As Object)
            ReDim Preserve _parameters(Me.Length)
            Dim param As SqlClient.SqlParameter = New SqlClient.SqlParameter(name, type)
            param.Value = value
            _parameters(Me.Length - 1) = param
        End Sub
    End Class
End Class
